/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.uefs.cedoc.db.estante;

/**
 *
 * @author Gregory Porto
 */
import br.uefs.cedoc.db.DBController;
import br.uefs.cedoc.db.exception.DBConnectionException;
import br.uefs.cedoc.entidades.EstanteBean;
import br.uefs.cedoc.excecao.DocRepositoryException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

/**
 *
 * @author Gregory Porto
 */
public class DBEstante {

    public void criarEstanteCivel(EstanteBean estante) throws  DBConnectionException,DocRepositoryException {

        try{
        String comandoSQL = "INSERT INTO estante (idestante,estante,serie_idserie) VALUES(?,?,?)";

        Connection conn = DBController.getInstance().getConnection();

        if (conn != null) {

            PreparedStatement prepStatement = conn.prepareStatement(comandoSQL);
            prepStatement.setInt(1, estante.getIdEstante());
            prepStatement.setString(2, estante.getEstante());
            prepStatement.setInt(3, estante.getIdSerie());

            //DBOperacoes dbo = new DBOperacoes();
            //secao.setIdsecao(dbo.getMaxID("idsecao", "secao"));	
            //System.out.println("IDSECAO= "+secao.getIdsecao());
            //prepStatement.setInt(1, secao.getIdsecao());
            prepStatement.execute();

            prepStatement.close();
        } else {
            DBConnectionException dbException = new DBConnectionException(" Erro de conexao com banco ");
            dbException.printStackTrace();
            throw dbException;
        }
        }catch (SQLException e){
                throw new DocRepositoryException(e.getMessage());
        }



    }

    public synchronized void editarEstante(EstanteBean estante) throws SQLException, DBConnectionException {

        String comandoSQL = "UPDATE estante SET estante = ? WHERE idestante = ?";

        Connection conn = DBController.getInstance().getConnection();
        //System.out.println("Editar acervo: "+acervo.getNome());
        if (conn != null) {

            PreparedStatement prepStatement = conn.prepareStatement(comandoSQL);
            prepStatement.setString(1, estante.getEstante());
            prepStatement.setInt(2, estante.getIdEstante());
            prepStatement.execute();
            prepStatement.close();

        } else {
            DBConnectionException dbException = new DBConnectionException(" Erro de conex�o com banco ");
            dbException.printStackTrace();
            throw dbException;
        }


    }

    public EstanteBean listaEstante(int id) throws SQLException, DBConnectionException {
        String comandoSQL = "SELECT * FROM estante WHERE idestante=?"; // incluir
        // limites

        Connection conn = DBController.getInstance().getConnection();
        try {
            if (conn != null) {
                PreparedStatement prepStatement = conn.prepareStatement(comandoSQL);

                prepStatement.setInt(1, id);
                ResultSet rs = prepStatement.executeQuery();



                while (rs.next()) {
                    EstanteBean estante = new EstanteBean();

                    estante.setIdEstante(rs.getInt("idestante"));
                    estante.setEstante(rs.getString("estante"));

                    return estante;

                }
                DBController.getInstance().closeAll(conn, null, null);



            } else {
                DBConnectionException dbException = new DBConnectionException(" Erro de conex�o com banco ");
                dbException.printStackTrace();
                throw dbException;
            }
            return null;
        } catch (NullPointerException ex) {
            ex.printStackTrace();
            return null;
        }
    }

    public EstanteBean getEstanteSerie(int idestante,int idserie) throws SQLException, DBConnectionException {
        String comandoSQL = "SELECT * FROM estante WHERE  idestante=? and serie_idserie=?"; // incluir
        // limites

        Connection conn = DBController.getInstance().getConnection();
        try {
            if (conn != null) {
                PreparedStatement prepStatement = conn.prepareStatement(comandoSQL);

                prepStatement.setInt(1, idestante);
                prepStatement.setInt(2, idserie);
                ResultSet rs = prepStatement.executeQuery();



                while (rs.next()) {
                    EstanteBean estante = new EstanteBean();

                    estante.setIdEstante(rs.getInt("idestante"));
                    estante.setEstante(rs.getString("estante"));
                    estante.setIdSerie(rs.getInt("serie_idserie"));

                    return estante;

                }
                DBController.getInstance().closeAll(conn, null, null);



            } else {
                DBConnectionException dbException = new DBConnectionException(" Erro de conex�o com banco ");
                dbException.printStackTrace();
                throw dbException;
            }
            return null;
        } catch (NullPointerException ex) {
            ex.printStackTrace();
            return null;
        }
    }

    public void removerEstante(int id) throws SQLException {

        String comandoSQL = "DELETE FROM estante WHERE idestante=?";

        Connection conn = DBController.getInstance().getConnection();

        if (conn != null) {


            PreparedStatement prepStatement = conn.prepareStatement(comandoSQL);
            prepStatement.setInt(1, id);
            prepStatement.execute();
            prepStatement.close();
        }

    }

    public EstanteBean[] listarEstantes(int idSerie) throws SQLException, DBConnectionException {
        String comandoSQL = "SELECT * FROM estante where serie_idserie=?"; // incluir
        // limites

        Connection conn = DBController.getInstance().getConnection();
        try {
            if (conn != null) {
                PreparedStatement prepStatement = conn.prepareStatement(comandoSQL);

                prepStatement.setInt(1,idSerie);
                ResultSet rs = prepStatement.executeQuery();

                Collection estantes = new ArrayList();

                while (rs.next()) {
                    EstanteBean estante_temp = new EstanteBean();

                    estante_temp.setIdEstante(rs.getInt("idestante"));
                    estante_temp.setEstante(rs.getString("estante"));
                    estantes.add(estante_temp);

                }
                DBController.getInstance().closeAll(conn, null, null);
                return (EstanteBean[]) estantes.toArray(new EstanteBean[0]);


            } else {
                DBConnectionException dbException = new DBConnectionException(" Erro de conex�o com banco ");
                dbException.printStackTrace();
                throw dbException;
            }
        } catch (NullPointerException ex) {
            ex.printStackTrace();
            return null;
        }
    }

    public int listaUltimaEstante() throws SQLException, DBConnectionException {
        String comandoSQL = "SELECT MAX(idestante) FROM estante";
        // limites
        Connection conn = DBController.getInstance().getConnection();
        PreparedStatement prepStatement = conn.prepareStatement(comandoSQL);

        ResultSet rs = prepStatement.executeQuery(comandoSQL);
        if (rs.next()) {
            return rs.getInt(1);
        }
        prepStatement.close();
        return -1;

    }
}

